import os
import openpyxl
from openpyxl.styles import PatternFill, Font
import datetime
import shutil
import pandas as pd

def copy_file(source, destination):
    try:
        shutil.copy(source, destination)
        print(f"{destination} 文件复制成功！")
    except IOError as e:
        print("文件复制失败:", e)

def letter_to_number(letter):
    if letter.isalpha():
        return ord(letter.upper()) - ord('A')
    else:
        return None

def get_first_character(string):
    if string:
        return string[0]
    else:
        return None

def get_list_from_excel(file_name, sheet_name=0, start_row=3):
    """
    Read an Excel file to get the row count and create a list starting from 4 up to row_count + 1

    Parameters:
    file_name (str): The path to the Excel file
    sheet_name (int/str): The sheet index or name to read from the Excel file

    Returns:
    list/str: A list of integers from 3 to row_count + 2, or an error message
    """
    try:
        df = pd.read_excel(file_name, sheet_name=sheet_name)
        row_count = len(df)
        return list(range(start_row, row_count + 2))
    except FileNotFoundError:
        return "File not found."
    except Exception as e:
        return f"An error occurred: {e}"

def get_today_row(file_path, sheetname, date_col, start_row=4):
    today_row = []
    before_today_row = []
    after_today_row = []

    wb = openpyxl.load_workbook(file_path, data_only=True)
    sheet = wb[sheetname]
    today = datetime.date.today()

    for row_idx, row in enumerate(sheet.iter_rows(min_row=start_row, values_only=True), start=start_row):
        date_value = row[date_col]
        if isinstance(date_value, datetime.datetime):
            date_value = date_value.date()

        if date_value is not None:
            if date_value == today:
                today_row.append(row_idx)
            elif date_value < today:
                before_today_row.append(row_idx)
            elif date_value > today:
                after_today_row.append(row_idx)

    wb.close()
    return today_row, before_today_row, after_today_row

def get_done_row(file_path, sheetname, done_col, start_row=4):
    done_row = []
    todo_row = []

    wb = openpyxl.load_workbook(file_path, data_only=True)
    sheet = wb[sheetname]

    for row_idx, row in enumerate(sheet.iter_rows(min_row=start_row, values_only=True), start=start_row):
        done_str = row[done_col]
        done_val = get_first_character(done_str) 

        if done_val == "√":
            done_row.append(row_idx)
        else:
            todo_row.append(row_idx)
    wb.close()
    return done_row, todo_row


def mark_color(file_path, sheetname, start_col, end_col, row_list, font_color):
    wb = openpyxl.load_workbook(file_path)
    sheet = wb[sheetname]
    font = Font(color=font_color)
    for row_idx in row_list:
        for col_idx in range(start_col + 1, end_col + 2):
            target_cell = sheet.cell(row=row_idx, column=col_idx)
            target_cell.font = font
    wb.save(file_path)

def mark_color_dict(file_path, sheetname, color_dict):
    wb = openpyxl.load_workbook(file_path)
    sheet = wb[sheetname]

    for key, config in color_dict.items():
        font_color = config.color
        start_col = letter_to_number(config.start)
        end_col = letter_to_number(config.end)
        row_list = config.rows
        font = Font(color=font_color)

        for row_idx in row_list:
            for col_idx in range(start_col+1, end_col + 2):
                target_cell = sheet.cell(row=row_idx, column=col_idx)
                target_cell.font = font

    wb.save(file_path)

class ColorConfig:
    def __init__(self, color, start, end):
        self.color = color
        self.start = start
        self.end = end
        self.rows = []

    def add_rows(self, row_list):
        self.rows.extend(row_list)

    def __repr__(self):
        return f"ColorConfig(color={self.color}, start={self.start}, end={self.end}, rows={self.rows})"

if __name__ == "__main__":
    black_color = "000000" # #000000
    red_color = "00FF0000" # #FF0000
    gray_color = "aeabab"  # #aeabab

    date1_letter   = "R"
    date2_letter   = "N"
    isdone1_letter = "M"
    isdone2_letter = "P"

    begin1_letter  = "H"
    end1_letter    = "M"
    begin2_letter  = "N"
    end2_letter    = "P"

    file_path = r"D:\project\time_management\时间规划.xlsx"
    sheetname = "Sheet1"
    dir_name = os.path.dirname(file_path) 
    file_name = os.path.basename(file_path)

    # output_file_path = os.path.join(dir_name, "color_" + file_name)
    # copy_file(file_path, output_file_path)

    init_row_list = get_list_from_excel(file_path, sheetname)
    today1_row_list, before_today1_row_list, after_today1_row_list = get_today_row(file_path, sheetname, letter_to_number(date1_letter))
    today2_row_list, before_today2_row_list, after_today2_row_list = get_today_row(file_path, sheetname, letter_to_number(date2_letter))
    done_row_list1, todo_row_list1 = get_done_row (file_path, sheetname, letter_to_number(isdone1_letter))
    done_row_list2, todo_row_list2 = get_done_row (file_path, sheetname, letter_to_number(isdone2_letter))

    # print("init_row_list =", init_row_list)
    # print("today1_row_list = ", today1_row_list)
    # print("today2_row_list = ", today2_row_list)
    # print("before_today1_row_list = ", before_today1_row_list)
    # print("before_today2_row_list = ", before_today2_row_list)
    # print("after_today1_row_list = ", after_today1_row_list)
    # print("after_today2_row_list = ", after_today2_row_list)
    # print("done_row_list1 = ", done_row_list1)
    # print("done_row_list2 = ", done_row_list2)
    # print("todo_row_list1 = ", todo_row_list1)
    # print("todo_row_list2 = ", todo_row_list2)


    # Red List: 在todo_row_list1中且同时在today1_row_list或before_today1_row_list中的元素
    red1_list = [item for item in todo_row_list1 if item in today1_row_list or item in before_today1_row_list]
    red2_list = [item for item in todo_row_list2 if item in today2_row_list or item in before_today2_row_list]

    # Gray List: 在done_row_list1中的元素
    gray1_list = done_row_list1
    gray2_list = done_row_list2

    # Black List: 在init_row_list中但不在red_list和gray_list中的元素
    black1_list = [item for item in init_row_list if item not in red1_list and item not in gray1_list]
    black2_list = [item for item in init_row_list if item not in red2_list and item not in gray2_list]


    black1_config   = ColorConfig(black_color, begin1_letter, end1_letter)
    black2_config   = ColorConfig(black_color, begin2_letter, end2_letter)
    red1_config     = ColorConfig(red_color  , begin1_letter, end1_letter)
    red2_config     = ColorConfig(red_color  , begin2_letter, end2_letter)
    gray1_config    = ColorConfig(gray_color , begin1_letter, end1_letter)
    gray2_config    = ColorConfig(gray_color , begin2_letter, end2_letter)

    black1_config.add_rows(black1_list)
    black2_config.add_rows(black2_list)
    red1_config.add_rows(red1_list)
    red2_config.add_rows(red2_list)
    gray1_config.add_rows(gray1_list)
    gray2_config.add_rows(gray2_list)

    color_dict = {
        "black1": black1_config,
        "black2": black2_config,
        "red1": red1_config,
        "red2": red2_config,
        "done1": gray1_config,
        "done2": gray2_config
    }

    for key, value in color_dict.items():
        print(f"{key}: {value}")

    mark_color_dict(file_path, sheetname, color_dict)

